import sys
import time
import numpy as np
import threading
import pandas as pd
from Task import Task
from SQLlitCRUD import SQLiteCRUD
import pandas as pd
from sqlalchemy import create_engine, text


path_dir = r"D:\ipythonworkspace\ManaAPI\example.db"
db = SQLiteCRUD(path_dir)
SQLALCHEMY_DATABASE_URL = f'sqlite:///{path_dir}'
engine = create_engine(
    SQLALCHEMY_DATABASE_URL,  echo=False, connect_args={'check_same_thread': False}
)

con = engine.connect()

lock = threading.Lock()

r_count = 0
conn = engine.connect()
def plusNum(event):
    global r_count
    if 1 == 1:
        lock.acquire()
        start = time.time()
        dt = db.select_data('count_num', "id=1")[0]
        num = dt[1]
        #db.update_data('count_num', {"value": num + 1}, {"id": 1}) # commit 这个最耗时
        rnum = np.random.randint(1,10)
        r_count += rnum
        print(f"{threading.current_thread().name} 访问DB耗时: {time.time() - start}")
        lock.release()

    else:
        lock.acquire()
        start = time.time()

        dt = pd.read_sql("select * from count_num where id=1", con=conn)
        #print(f"{threading.current_thread().name}:{dt}")
        num = dt.iloc[0]['value']
        stmt = text(f"update count_num set value={num + 1} where id=1")
        conn.execute(stmt)
        conn.commit() # commit 这个最耗时
        rnum = np.random.randint(1,10)
        rnum = 1

        r_count += rnum
        print(f"{threading.current_thread().name} 访问DB耗时: {time.time() - start}")

        lock.release()

        time.sleep(rnum)
        print(f"{threading.current_thread().name}:{num+1} {rnum}")


#print(plusNum(1))

#input()

t = Task(5, 10, plusNum)
start = time.time()
print(start)
t.switch.start()

time.sleep(.3)
t.switch.pause()
time.sleep(3)
t.switch.resume()

t.switch.join()

print(r_count)
print(time.time()-start)